package com.demo2do.fms.admin.entity.report.business;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import java.util.WeakHashMap;

import org.springframework.jdbc.core.RowMapper;

import com.demo2do.core.persistence.support.QueryBuilder;
import com.demo2do.core.persistence.support.QueryFilter;
import com.demo2do.core.persistence.support.QueryOrder;

/**
 *
 * @author Ark
 *
 */
public class ConsignorShipmentReportSearch implements QueryBuilder {

    /** 所选择公司 */
    private Long organizationId;
    
    /** 调度指令号 */
    private String loadNumber;
    
    /** 驳运车Id */
    private Long transMeansId;
    
    /** 承接方Id */
    private Long companyId;
    
    /** 最终客户 */
    private Long customerId;
    
    /** 起始地  */
    private Long srcWhId;
    
    /**  目的地 */
    private Long destWhId;
    
    /**  是否暂估 */
    private Boolean temporary;
    
    /** 起始行 */
    private Integer beginIndex;
    
    /** 截止行 */
    private Integer endIndex;
    
    private QueryOrder queryOrder;
    
    private QueryFilter queryFilter;
    
    /**
     * the default constructor
     */
    public ConsignorShipmentReportSearch() {
        queryOrder = new QueryOrder();
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getPreparedFilter(boolean)
     */
    public QueryFilter getPreparedFilter(boolean refresh) {
        
        if(queryFilter == null || refresh) {

            queryFilter = new QueryFilter().addEquals("f", "load_number", loadNumber)
                                           .addEquals("v", "id", transMeansId)
                                           .addEquals("f", "is_temporary", temporary)
                                           .addEquals("f", "consignor_id", organizationId)
                                           .addEquals("f", "company_id", companyId)
                                           .addEquals("s", "customer_id", customerId)
                                           .addEquals("f", "src_wh_id", srcWhId)
                                           .addEquals("f", "dest_wh_id", destWhId);
        }
        
        return queryFilter;
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildSentenceTemplate()
     */
    public String buildSentenceTemplate() {
        
        StringBuffer buffer = new StringBuffer(512);
        
        buffer.append("SELECT consignor.organization_name AS consignorName, ")
                     .append("company.organization_name AS companyName, ")
                     .append("client.organization_name AS clientName, ")
                     .append("s.load_number AS loadNumber, ")
                     .append("v.vehicle_code || ' - ' || v.vehicle_name AS vehicle, ")
                     .append("wa.facility_code || ' - ' || wa.facility_name AS srcFacility, ")
                     .append("wb.facility_code || ' - ' || wb.facility_name AS destFacility, ")
                     .append("f.fee, ")
                     .append("f.fee_rate AS feeRate, ")
                     .append("f.kilometer, ")
                     .append("car.product_category_name AS productCategoryName, ")
                     .append("color.feature_name AS colorName, ")
                     .append("FC_GET_VINCODE_BY_SHIPMENT_ID(s.shipment_id) AS vincode, ")
                     .append("f.is_temporary AS temporary ")
             .append("FROM tbl_all_finance f, tbl_all_shipment s, vw_consignor consignor, vw_transportation_company company, vw_client client, vw_warehouse wa, vw_warehouse wb, vw_car_category car, vw_car_color color, vw_vehicle v ")
             .append("WHERE f.is_disabled = 0 AND s.is_disabled = 0 AND f.is_balanced = 0 AND consignor.ID = f.consignor_id AND company.ID = f.company_id AND client.ID = s.customer_id AND wa.ID = f.src_wh_id AND wb.ID = f.dest_wh_id AND car.ID = s.vh_cartype_id AND color.ID = s.vh_color_id AND v.id = s.TRANS_MEANS_ID AND s.shipment_id = f.shipment_id AND s.load_number = f.load_number AND s.status > 1")
             .append(getPreparedFilter(false).clauseSentence() + queryOrder.sentence());
        
        String sentence = "SELECT page.*, ROWNUM rn FROM (" + buffer.toString() + ") page WHERE ROWNUM <= " + endIndex;
        sentence = "SELECT * FROM (" + sentence + ") WHERE rn >= " + beginIndex;
        
        return sentence;
        
    }
    
    /**
     * build rowMapper
     * 
     * @return
     */
    public RowMapper<Map<String, Object>> buildRowMapper() {
        
        RowMapper<Map<String, Object>> rowMapper = new RowMapper<Map<String,Object>>() {
            
            /* (non-Javadoc)
             * @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet, int)
             */
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                Map<String, Object> result = new WeakHashMap<String, Object>();
                
                result.put("consignorName", rs.getObject("consignorName"));
                result.put("companyName", rs.getObject("companyName"));
                result.put("clientName", rs.getObject("clientName"));
                result.put("loadNumber", rs.getObject("loadNumber"));
                result.put("vehicle", rs.getObject("vehicle"));
                result.put("srcFacility", rs.getObject("srcFacility"));
                result.put("destFacility", rs.getObject("destFacility"));
                result.put("fee", rs.getObject("fee"));
                result.put("feeRate", rs.getObject("feeRate"));
                result.put("kilometer", rs.getObject("kilometer"));
                result.put("productCategoryName", rs.getObject("productCategoryName"));
                result.put("colorName", rs.getObject("colorName"));
                result.put("vincode", rs.getObject("vincode"));
                result.put("temporary", rs.getBoolean("temporary"));
                
                return result;
                
            }
            
        };
        
        return rowMapper;
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildParameters()
     */
    public Map<String, Object> buildParameters() {
        return getPreparedFilter(false).parameters();
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getOrder()
     */
    public QueryOrder getOrder() {
        return queryOrder;
    }

    /**
     * @return the organizationId
     */
    public Long getOrganizationId() {
        return organizationId;
    }

    /**
     * @return the loadNumber
     */
    public String getLoadNumber() {
        return loadNumber;
    }

    /**
     * @return the transMeansId
     */
    public Long getTransMeansId() {
        return transMeansId;
    }

    /**
     * @return the companyId
     */
    public Long getCompanyId() {
        return companyId;
    }

    /**
     * @return the customerId
     */
    public Long getCustomerId() {
        return customerId;
    }

    /**
     * @return the srcWhId
     */
    public Long getSrcWhId() {
        return srcWhId;
    }

    /**
     * @return the destWhId
     */
    public Long getDestWhId() {
        return destWhId;
    }

    /**
     * @return the temporary
     */
    public Boolean getTemporary() {
        return temporary;
    }
    
    /**
     * @return
     */
    public Integer getBeginIndex() {
        return beginIndex;
    }

    /**
     * @return
     */
    public Integer getEndIndex() {
        return endIndex;
    }
    
    /**
     * @param organizationId the organizationId to set
     */
    public void setOrganizationId(Long organizationId) {
        this.organizationId = organizationId;
    }

    /**
     * @param loadNumber the loadNumber to set
     */
    public void setLoadNumber(String loadNumber) {
        this.loadNumber = loadNumber;
    }

    /**
     * @param transMeansId the transMeansId to set
     */
    public void setTransMeansId(Long transMeansId) {
        this.transMeansId = transMeansId;
    }

    /**
     * @param companyId the companyId to set
     */
    public void setCompanyId(Long companyId) {
        this.companyId = companyId;
    }

    /**
     * @param customerId the customerId to set
     */
    public void setCustomerId(Long customerId) {
        this.customerId = customerId;
    }

    /**
     * @param srcWhId the srcWhId to set
     */
    public void setSrcWhId(Long srcWhId) {
        this.srcWhId = srcWhId;
    }

    /**
     * @param destWhId the destWhId to set
     */
    public void setDestWhId(Long destWhId) {
        this.destWhId = destWhId;
    }

    /**
     * @param temporary the temporary to set
     */
    public void setTemporary(Boolean temporary) {
        this.temporary = temporary;
    }
    
    /**
     * @param beginIndex
     */
    public void setBeginIndex(Integer beginIndex) {
        this.beginIndex = beginIndex;
    }
    
    /**
     * @param endIndex
     */
    public void setEndIndex(Integer endIndex) {
        this.endIndex = endIndex;
    }
    
}
